﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SqlServerCe;
using System.Data;
using System.Data.OleDb;
using ErikEJ.SqlCe;



namespace PAPI_Import
{
    class Program
    {
        static int Main(string[] args)
        {
            //STEP 0 - Settings
            AppDomain.CurrentDomain.SetData("DataDirectory", AppDomain.CurrentDomain.BaseDirectory + "..\\..\\App_Data");
            DateTime CurrDate = System.DateTime.Now;//.ToString("yyyy.MM.dd");

            try
            {
                StreamWriter writer= new StreamWriter("C:\\webs\\papi_import\\PAPI_Import\\App_Data\\Log.txt", true);

                writer.WriteLine(""); writer.WriteLine("");
                writer.WriteLine("Procedure start at: {0}", CurrDate);

                //STEP 1 - Import Excel to Side Table
                importDataFromExcel("POes", "C:\\webs\\papi_import\\PAPI_Import\\App_Data\\SAP_POes.xls",true); //INIT MAIN DB
                importDataFromExcel("PO_Import","C:\\webs\\papi_import\\PAPI_Import\\App_Data\\SAP_EXPORT.xls",false);
                
                //STEP 2 - Handle new POs
                writer.WriteLine("New POs Entered into system: {0}", HandleNewPOs());

                //STEP 3 - Handle closed POs
                writer.WriteLine("Closed POs updated: {0}", HandleClosedPOs());

                //STEP 4 - Handle POs that changed
                writer.WriteLine("POs delivery date updated: {0}", HandleUpdatedPOs());

                //STEP 5 - Finish
                writer.WriteLine("Procedure finish at: {0}", CurrDate);
                writer.WriteLine("-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=");
                writer.Close();
            }
            catch (IOException e)
            {
                Console.WriteLine("IOException. {0} Exception caught.", e);
                return -1;
            }

            catch (Exception e)
            {
                Console.WriteLine("Generel Exception. {0} Exception caught.", e);
                return -1;
            }            

            Console.WriteLine("SUCCESS");
            
            return 0;
            //Console.ReadKey(true);
           
        }

        static void importDataFromExcel(string sSQLCeTable, string excelFilePath,bool withStatus=false)
        {
            // make sure your sheet name is correct, here sheet name is Sheet1, so you can change your sheet name if have different

            string myExcelDataQuery;
            if (withStatus)
            {
                myExcelDataQuery = "Select PONumber,Item,Descr,VendorName,DeliveryDate,Status from [Sheet1$]";
            }
            else
            {
                myExcelDataQuery = "Select PONumber,Item,Descr,VendorName,DeliveryDate from [Sheet1$]";
            }

            //Create our connection strings
            string sExcelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath + ";Extended Properties=" + "\"Excel 8.0;HDR=YES;\"";

            //string sSqlConnectionString = "SERVER=MyDatabaseServerName;USER ID=DBUserId;PASSWORD=DBUserPassword;DATABASE=DatabaseName;CONNECTION RESET=FALSE";
            string sSqlCeConnectionString = @"Data Source=|DataDirectory|\POs.sdf";

            //Execute a query to erase any previous data from our destination table
            string sClearSQL = "DELETE FROM " + sSQLCeTable;
            SqlCeConnection SqlCeConn = new SqlCeConnection(sSqlCeConnectionString);
            SqlCeCommand SqlCeCmd = new SqlCeCommand(sClearSQL, SqlCeConn);
            SqlCeConn.Open();
            SqlCeCmd.ExecuteNonQuery();
            SqlCeConn.Close();

            //Series of commands to bulk copy data from the excel file into our SQL table
            OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);
            OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn);
            OleDbConn.Open();
            OleDbDataReader dr = OleDbCmd.ExecuteReader();

            SqlCeBulkCopy bulkCopy = new SqlCeBulkCopy(sSqlCeConnectionString);
            bulkCopy.DestinationTableName = sSQLCeTable;
            bulkCopy.WriteToServer((System.Data.IDataReader)dr);

            /*
            int counter = 0;
            while ((!dr.IsClosed) && dr.Read())
            {
                counter++;
                bulkCopy.WriteToServer((System.Data.IDataReader)dr);
            }
             */

            //SqlCeConn.Close();
            OleDbConn.Close();
        }

        static int HandleNewPOs()
        {
            string sSqlCeConnectionString = @"Data Source=|DataDirectory|\POs.sdf";
            //string sSQLCeTable = "PO_Import";
            


            //Execute a query to erase any previous data from our destination table

            SqlCeConnection SqlCeConn = new SqlCeConnection(sSqlCeConnectionString);

            string sClearSQL = @"   SELECT PONumber, Item, Descr, VendorName, DeliveryDate
                                    FROM PO_Import
                                    WHERE NOT EXISTS (
                                    SELECT *
                                    FROM POes
                                    WHERE PONumber = PO_Import.PONumber
                                    AND Item = PO_Import.Item)";

            SqlCeCommand SqlCeCmd = new SqlCeCommand(sClearSQL, SqlCeConn);

            string sInsertSQL = @"  INSERT INTO POes(PONumber,Item,Descr,VendorName,DeliveryDate,Status)
                                                VALUES (@PONumber,@Item,@Descr,@VendorName,@DeliveryDate,0)";

            SqlCeCommand SqlCeInsertCmd = new SqlCeCommand(sInsertSQL, SqlCeConn);
            
            SqlCeConn.Open();

            SqlCeDataReader dr = SqlCeCmd.ExecuteReader();
            

            int counter = 0;
            
            while (dr.Read())
            {
                counter++;
                SqlCeInsertCmd.Parameters.Clear();
                SqlCeInsertCmd.Parameters.AddWithValue("PONumber",dr.GetInt32(0));
                SqlCeInsertCmd.Parameters.AddWithValue("Item", dr.GetInt32(1));
                SqlCeInsertCmd.Parameters.AddWithValue("Descr", dr.GetString(2));
                SqlCeInsertCmd.Parameters.AddWithValue("VendorName", dr.GetString(3));
                SqlCeInsertCmd.Parameters.AddWithValue("DeliveryDate", dr.GetSqlDateTime(4));
                SqlCeInsertCmd.ExecuteNonQuery();
            }
            
            dr.Close();
            SqlCeConn.Close();

            return counter;
        }

        static int HandleClosedPOs()
        {
            string sSqlCeConnectionString = @"Data Source=|DataDirectory|\POs.sdf";
            SqlCeConnection SqlCeConn = new SqlCeConnection(sSqlCeConnectionString);
            
            string sClearSQL = @"   SELECT PONumber, Item
                                    FROM POes
                                    WHERE NOT EXISTS (
                                    SELECT *
                                    FROM PO_Import
                                    WHERE PONumber = POes.PONumber
                                    AND Item = POes.Item)";

            SqlCeCommand SqlCeCmd = new SqlCeCommand(sClearSQL, SqlCeConn);

            string sClearSQL2 = @"  UPDATE POes 
                                    SET Status=99 
                                    WHERE PONumber = @PONumber AND Item=@Item";

            SqlCeCommand SqlCeCmd2 = new SqlCeCommand(sClearSQL2, SqlCeConn);

            SqlCeConn.Open();

            SqlCeDataReader dr = SqlCeCmd.ExecuteReader();

            int counter = 0;

            while (dr.Read())
            {
                counter++;
                SqlCeCmd2.Parameters.Clear();
                SqlCeCmd2.Parameters.AddWithValue("PONumber", dr.GetInt32(0));
                SqlCeCmd2.Parameters.AddWithValue("Item", dr.GetInt32(1));
                SqlCeCmd2.ExecuteNonQuery();
            }

            dr.Close();
            SqlCeConn.Close();

            return counter;
        }

        static int HandleUpdatedPOs()
        {
            string sSqlCeConnectionString = @"Data Source=|DataDirectory|\POs.sdf";
            SqlCeConnection SqlCeConn = new SqlCeConnection(sSqlCeConnectionString);

            string sClearSQL = @"   SELECT PONumber, Item, DeliveryDate
                                    FROM PO_Import
                                    WHERE EXISTS (
                                    SELECT *
                                    FROM POes
                                    WHERE PONumber = PO_Import.PONumber
                                    AND Item = PO_Import.Item
                                    AND DeliveryDate <> PO_Import.DeliveryDate)";
            
            SqlCeCommand SqlCeCmd = new SqlCeCommand(sClearSQL, SqlCeConn);

            string sClearUpdateSQL = @" UPDATE POes 
                                        SET DeliveryDate = @DeliveryDate
                                        WHERE PONumber = @PONumber AND Item = @Item";

            SqlCeCommand SqlCeUpdateCmd = new SqlCeCommand(sClearUpdateSQL, SqlCeConn);


            SqlCeConn.Open();

            SqlCeDataReader dr = SqlCeCmd.ExecuteReader();

            int counter = 0;

            while (dr.Read())
            {
                counter++;
                SqlCeUpdateCmd.Parameters.Clear();
                SqlCeUpdateCmd.Parameters.AddWithValue("PONumber", dr.GetInt32(0));
                SqlCeUpdateCmd.Parameters.AddWithValue("Item", dr.GetInt32(1));
                SqlCeUpdateCmd.Parameters.AddWithValue("DeliveryDate", dr.GetDateTime(2));
                SqlCeUpdateCmd.ExecuteNonQuery();
            }

            dr.Close();
            SqlCeConn.Close();

            return counter;
        } 
    }
}
